package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import dao.MenuDao;
import entity.Menu;
import util.DBUtils;

public class MenuDaoImpl implements MenuDao{

	// 1.得到连接
	Connection c = DBUtils.getConnection();
	
	@Override
	public boolean insert(Menu menu) {
		// 1.创建sql文
		String sql = "insert into menu(name,price,spicy,deleteflag) values(?,?,?,false)";
		int row = 0;
		
		try {
			// 2.创建句柄
											// 如果想得到自动增长的主键，有第二个参数 Statement.RETURN_GENERATED_KEYS
			PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			// 3.设置参数
			ps.setObject(1, menu.getName());
			ps.setObject(2, menu.getPrice());
			ps.setObject(3, menu.isSpicy());
			// 4.执行
			row = ps.executeUpdate();
			
			// 执行完后，得到自动增长主键
			ResultSet keySet = ps.getGeneratedKeys();
			while(keySet.next()) {
				// 因为主键是int的自动增长所以getInt
				System.out.println(keySet.getInt(1));
			}
			
			// 5.关闭句柄
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 6.返回
		return row == 1 ? true : false;
	}

	@Override
	public boolean update(Menu menu) {
		String sql = "update menu set name=?,price=?,spicy=? where id=?";
		int row = 0;
		
		try {
			PreparedStatement ps = c.prepareStatement(sql);
			
			ps.setObject(1,menu.getName());
			ps.setObject(2, menu.getPrice());
			ps.setObject(3, menu.isSpicy());
			ps.setObject(4, menu.getId());
			
			row = ps.executeUpdate();
			
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return row == 1;
	}

	@Override
	public boolean deleteById(int id) {
		String sql = "update menu set deleteflag=1 where id=? and deleteflag=0";
		int row = 0;
		try {
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setObject(1, id);
			row =  ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row == 1;
	}

	@Override
	public Menu selectById(int id){
		String sql = "select * from menu where id= ? and deleteflag=false";
		
		try {
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setObject(1, id);
			ResultSet set = ps.executeQuery();
			if (set.next()) {
				// 结果集封装给一个对象
				Menu m = new Menu();
				m.setId(set.getInt(1));
				m.setName(set.getString(2));
				m.setPrice(set.getBigDecimal(3));
				m.setSpicy(set.getBoolean(4));
				return m;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Menu> selectByName(String name) {
//		String sql = "select * from menu where name like ? and deleteflag=0";
		String sql = "select * from menu where instr(name,?)>0 and deleteflag=0";
		List<Menu> list = new ArrayList<>();
		
		try {
			PreparedStatement ps = c.prepareStatement(sql);
//			ps.setObject(1, "%"+name+"%");
			ps.setObject(1, name);
			ResultSet set = ps.executeQuery();
			while (set.next()) {
				// 结果集封装给一个对象
				Menu m = new Menu();
				m.setId(set.getInt(1));
				m.setName(set.getString(2));
				m.setPrice(set.getBigDecimal(3));
				m.setSpicy(set.getBoolean(4));
				
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 判断是否空集合，如果为空则返回Collections.emptyList()，否则返回list
		return list.isEmpty() ? Collections.emptyList() : list ;
	}

}
